package fy.erp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import fy.config.FyConfig;
import fy.erp.bean.OrderContractBean;
import fy.erp.bean.OrderContractLineBean;
import fy.erp.bean.SalesContractBean;
import fy.erp.bean.SalesContractLineBean;
import fy.util.DBUtil;
import fy.util.MyUtil;


public class QueryOrderContractDao {

	private static final String queryOrderContractHeadSQL = "SELECT  distinct head.req_number 计划单号, head.req_inv_org_name 请求部门, head.req_inv_org_code 计划库存组织,line.actual_mfg_org_code 生产组织, head.customer_number 客户号,head.customer_name 客户名称, head.address 发运地址,head.req_source_disp 来源, head.under_tolerance 允差下限,head.over_tolerance 允差上限, head.MEMO 备注, (select fu.description from apps.FND_USER  fu where fu.user_id =head.created_by)  制作用户   FROM apps.fyg_product_req_lines_v line,apps.fyg_product_req_headers_v head WHERE 1=1 and  head.prod_req_header_id = line.prod_req_header_id AND head.req_number=?";
	private static final String queryOrderContractLineSQL = "SELECT line.line_num 行号,line.actual_mfg_org_code 生产组织, line.item_name 本厂编号,line.item_desc 产品名称,(select part_num from apps.fyg_syn_item where attribute2=mp.attribute6  and item_number=line.item_name) 零件号,  line.actual_req_date 计划日期,line.mix_flag 混箱标识,  (select sum(ol_ordered_quantity) from  apps.FYG_ORDER_REQ_MATCH where req_line_id=line.prod_req_line_id) 销售订单数量,  line.REQ_QUANTITY 计划单数量,   (select sum(OL_RESERVATION_QUANTITY) from  apps.FYG_ORDER_REQ_MATCH where req_line_id=line.prod_req_line_id) 成品扣库数量,  LINE.under_tolerance 允差下限,LINE.over_tolerance 允差上限,  (select decode(PLANNING_MAKE_BUY_CODE,'1','制造','购买') FROM apps.mtl_system_items_b where segment1=line.item_name and organization_id=109) 属性,  line.MEMO 备注,  sum(line.REQ_QUANTITY) over(partition by line.mix_flag) 合计   FROM apps.mtl_parameters mp,  apps.fyg_product_req_headers_v head, apps.fyg_product_req_lines_v line WHERE 1=1 and mp.organization_id=line.ACTUAL_MFG_ORG_ID and line.status in ('15','20','30','40','50') and head.prod_req_header_id = line.prod_req_header_id   AND head.req_number =? ORDER BY line.line_num asc";

	private OrderContractBean orderContractBean;



	public OrderContractBean getOrderContractBean() {
		return orderContractBean;
	}

	public void query(String number) throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.connect(FyConfig.getERP_DB_CONFIG());

			pst = conn.prepareStatement(queryOrderContractHeadSQL);
			pst.setString(1, number);
			//pst.setString(2, factory);

			rs = pst.executeQuery();

			if (rs.next()) {
				// distinct head.req_number 计划单号, head.req_inv_org_name 请求部门,
				// head.req_inv_org_code 计划库存组织,line.actual_mfg_org_code 生产组织,
				// head.customer_number 客户号,head.customer_name 客户名称,
				// head.address 发运地址,head.req_source_disp 来源,
				// head.under_tolerance 允差下限,head.over_tolerance 允差上限, head.MEMO
				// 备注, (select fu.description from apps.FND_USER fu where
				// fu.user_id =head.created_by) 制作用户
				orderContractBean = new OrderContractBean();
				orderContractBean.setReq_number(rs.getString(1));
				orderContractBean.setReq_inv_org_name(rs.getString(2));
				orderContractBean.setReq_inv_org_code(rs.getString(3));
				orderContractBean.setActual_mfg_org_code(rs.getString(4));
				orderContractBean.setCustomer_number(rs.getString(5));
				orderContractBean.setCustomer_name(rs.getString(6));
				orderContractBean.setAddress(rs.getString(7));
				orderContractBean.setReq_source_disp(rs.getString(8));
				orderContractBean.setUnder_tolerance(rs.getString(9));
				orderContractBean.setOver_tolerance(rs.getString(10));
				orderContractBean.setMemo(rs.getString(11));
				orderContractBean.setCreated_user(rs.getString(12));

				DBUtil.close(rs, pst);

				pst = conn.prepareStatement(queryOrderContractLineSQL);
				pst.setString(1, number);
				//pst.setString(2, factory);
				// SELECT line.line_num 行号,line.actual_mfg_org_code 生产组织, line.item_name 本厂编号,
				// line.item_desc产品名称,(select part_num from apps.fyg_syn_item where
				// attribute2=mp.attribute6 and item_number=line.item_name) 零件号,
				// line.actual_req_date 计划日期,line.mix_flag 混箱标识, (select
				// sum(ol_ordered_quantity) from apps.FYG_ORDER_REQ_MATCH where
				// req_line_id=line.prod_req_line_id) 销售订单数量, line.REQ_QUANTITY
				// 计划单数量, (select sum(OL_RESERVATION_QUANTITY) from
				// apps.FYG_ORDER_REQ_MATCH where
				// req_line_id=line.prod_req_line_id) 成品扣库数量,
				// LINE.under_tolerance 允差下限,LINE.over_tolerance 允差上限, (select
				// decode(PLANNING_MAKE_BUY_CODE,'1','制造','购买') FROM
				// apps.mtl_system_items_b where segment1=line.item_name and
				// organization_id=109) 属性, line.MEMO 备注, sum(line.REQ_QUANTITY)
				// over(partition by line.mix_flag) 合计

				rs = pst.executeQuery();
				List<OrderContractLineBean> lines = new ArrayList<OrderContractLineBean>();
				while (rs.next()) {
					OrderContractLineBean line = new OrderContractLineBean();
					line.setLine_num(rs.getString(1));
					line.setActual_mfg_org_code(rs.getString(2));
					line.setItem_name(rs.getString(3));
					line.setItem_desc(rs.getString(4));
					line.setPart_num(rs.getString(5));
					line.setActual_req_date(MyUtil.getDate(rs.getString(6)));
					line.setMix_flag(rs.getString(7));
					line.setSum_ol_ordered_quantity(rs.getString(8));
					line.setREQ_QUANTITY(rs.getString(9));
					line.setSum_OL_RESERVATION_QUANTITY(rs.getString(10));
					line.setUnder_tolerance(rs.getString(11));
					line.setOver_tolerance(rs.getString(12));
					line.setPLANNING_MAKE_BUY_CODE(rs.getString(13));
					line.setMEMO(rs.getString(14));

					if ("Y".equals(line.getMix_flag())) {
						orderContractBean.setMix_sum(rs.getString(15));
					} else {
						orderContractBean.setSign_sum(rs.getString(15));
					}
					lines.add(line);
				}
				orderContractBean.setLines(lines);
			}

		} finally {
			DBUtil.close(rs, pst, conn);
		}
	}

}
